package com.example.mapper;

import com.example.pojo.Discuss;
import com.example.pojo.Posts;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author ${author}
 * @since 2024-06-12
 */
public interface PostsMapper extends BaseMapper<Posts> {
    //pc 回复总和  ac 点赞数  bc 投币 fc 分享
    @Select("select p.*,b.bname,b.tid,t.tname,u.name,\n" +
            "\tIFNULL((select count(*) from discuss where pid = p.pid),0) as pc,\n" +
            "\t IFNULL((SELECT SUM(likes) FROM likes WHERE pid = p.pid), 0) AS ac,\n" +
            "\t IFNULL((SELECT SUM(coins) FROM likes WHERE pid = p.pid), 0) AS bc,\n" +
            "\t IFNULL((SELECT SUM(shares) FROM likes WHERE pid = p.pid), 0) AS fc,\n" +
            "\t (IFNULL(2*(SELECT SUM(likes) FROM likes WHERE pid = p.pid), 0) +\n" +
            "     IFNULL(4*(SELECT SUM(coins) FROM likes WHERE pid = p.pid), 0) +\n" +
            "     IFNULL(5*(SELECT SUM(shares) FROM likes WHERE pid = p.pid), 0) +\n" +
            "\t\t IFNULL((select count(*) from discuss where pid = p.pid),0)) AS score\n" +
            "\t \n" +
            "\t\n" +
            " from posts p \n" +
            "join  bars b on p.bid = b.bid join post_types t on b.tid = t.tid \n" +
            "            join users u on p.uid = u.uid  ORDER BY p.tdate DESC")
    List<Posts> getAll();

    @Insert("insert into posts (uid,bid,title,content,images,tdate) values (#{uid},#{bid},#{title},#{content},#{images},#{tdate})")
    void addPost(@RequestParam("uid") Integer uid,@RequestParam("bid") Integer bid,@RequestParam("title") String title,
                 @RequestParam("content") String content,@RequestParam("images") String images,String tdate);

    @Select("select c.*,u.name,u.image from discuss c join users u on c.uid = u.uid  where c.pid = #{pid} ORDER BY c.cdate ASC")
    List<Discuss> getDiscuss(@RequestParam("pid") Integer pid);

    @Insert("insert into discuss (uid,text,cdate,pid) values (#{uid},#{text},#{cdate},#{pid})")
    void addDiscuss(@RequestParam("uid") Integer uid,@RequestParam("text") String text,String cdate,@RequestParam("pid") Integer pid);

    @Select("SELECT p.*, u.name, u.image, e.level, e.levelname,\n" +
            "    (SELECT COUNT(*) FROM discuss WHERE pid = p.pid) AS pc,\n" +
            "    (SELECT COUNT(*) FROM attention WHERE otherid = u.uid) AS ac,\n" +
            "    (SELECT COUNT(*) FROM follow WHERE uid = u.uid) AS fc,\n" +
            "\t\t(select count(*) from attention where uid = #{uid} and otherid = u.uid) as bc\n" +
            "FROM posts p\n" +
            "JOIN users u ON p.uid = u.uid\n" +
            "JOIN bars b ON b.bid = p.bid\n" +
            "JOIN user_details d ON d.uid = u.uid\n" +
            "JOIN ed e ON d.experience >= e.ex\n" +
            "    AND NOT EXISTS (\n" +
            "        SELECT 1\n" +
            "        FROM ed e2\n" +
            "        WHERE d.experience >= e2.ex AND e2.ex > e.ex\n" +
            "    )\n" +
            "WHERE b.bid = #{bid}\n" +
            "ORDER BY tdate DESC;")
    List<Posts> getDiscuss_Bar(@RequestParam("bid") Integer bid,@RequestParam("uid") Integer uid);

    @Select("select p.*,u.name,(select count(*) from discuss WHERE pid = p.pid) AS fc from posts p join users u on p.uid = u.uid  where p.uid = #{uid} ORDER BY tdate DESC")
    List<Posts> getDiscuss_User(@RequestParam("uid") Integer uid);

    @Select("select p.*,b.bname,t.tname,u.name,u.uid,\n" +
            "\tIFNULL((select count(*) from discuss where pid = p.pid),0) as pc,\n" +
            "\t IFNULL((SELECT SUM(likes) FROM likes WHERE pid = p.pid), 0) AS ac,\n" +
            "\t IFNULL((SELECT SUM(coins) FROM likes WHERE pid = p.pid), 0) AS bc,\n" +
            "\t IFNULL((SELECT SUM(shares) FROM likes WHERE pid = p.pid), 0) AS fc,\n" +
            "\t (IFNULL(2*(SELECT SUM(likes) FROM likes WHERE pid = p.pid), 0) +\n" +
            "     IFNULL(4*(SELECT SUM(coins) FROM likes WHERE pid = p.pid), 0) +\n" +
            "     IFNULL(5*(SELECT SUM(shares) FROM likes WHERE pid = p.pid), 0) +\n" +
            "\t\t IFNULL((select count(*) from discuss where pid = p.pid),0)) AS score,\n" +
            "\t\tIFNULL((SELECT likes FROM likes WHERE pid = p.pid AND uid = u.uid),0) AS likes,\n" +
            "\t\tIFNULL((SELECT shares FROM likes WHERE pid = p.pid AND uid = u.uid),0) AS shares,\n" +
            "\t\tIFNULL((SELECT coins FROM likes WHERE pid = p.pid AND uid = u.uid),0) AS coins\n" +
            "\t\n" +
            " from posts p \n" +
            "join  bars b on p.bid = b.bid join post_types t on b.tid = t.tid \n" +
            "            join users u on p.uid = u.uid   where pid = #{pid}      ORDER BY p.tdate DESC ")
    Posts findPostByPid(Integer pid);

    @Select("")
    Posts getDiscuss_Pid(@RequestParam("pid") Integer pid);

    @Delete("delete from discuss where cid = #{cid}")
    void delDis(@RequestParam("cid") Integer cid);

    @Select("select count(*) from discuss")
    Integer getAllDis();
}
